SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO






CREATE PROCEDURE [dbo].[usp_UpdateEmployerByNo]
      (
        @JOL_Employer_Account VARCHAR(50) ,
        @JOL_Employer_Password VARCHAR(50) ,
        @JOL_Employer_Name VARCHAR(50) ,
        @JOL_Employer_Industry VARCHAR(50) ,
        @JOL_Employer_Type VARCHAR(50) ,
        @JOL_Employer_Province VARCHAR(50) ,
        @JOL_Employer_City VARCHAR(50) ,
        @JOL_Employer_Address VARCHAR(50) ,
        @JOL_Employer_ZipCode VARCHAR(50) ,
        @JOL_Employer_HomePage VARCHAR(255) ,
        @JOL_Employer_Introduction NTEXT ,
        @JOL_Employer_Linkman VARCHAR(50) ,
        @JOL_Employer_Mobile VARCHAR(50) ,
        @JOL_Employer_Email VARCHAR(50) ,
        @JOL_Employer_Phone VARCHAR(50) ,
        @JOL_Employer_Fax VARCHAR(50) ,
        @JOL_Employer_VerifyStatus VARCHAR(50) ,
        @JOL_Employer_Credentials VARCHAR(50) ,
        @JOL_Employer_IsSchoolCorperate BIT ,
        @JOL_Employer_IsContactVisible BIT ,
        @JOL_Employer_IsVIP BIT ,
        @JOL_Employer_CredentialsFileLink VARCHAR(255) ,
        @JOL_Employer_UpdateUser VARCHAR(50) ,
        @JOL_Employer_VerifyUser VARCHAR(50),
        @JOL_Employer_No Int
      )
AS 

	declare @accountCount int
	set @accountCount =
	(
		select count(*) from JOL_Employer
		where JOL_Employer_No <> @JOL_Employer_No
		and (JOL_Employer_Account=@JOL_Employer_Account)
	)
	declare @nameCount int
	set @nameCount =
	(
		select count(*) from JOL_Employer
		where JOL_Employer_No <> @JOL_Employer_No
		and (JOL_Employer_Name=@JOL_Employer_Name)
	)
	  DECLARE @Pwd VARCHAR(50)
	  SET @Pwd = (
				   SELECT   JOL_Employer_Password
				   FROM     dbo.JOL_Employer
				   WHERE    JOL_Employer_Account = @JOL_Employer_Account
				 )
	  UPDATE    dbo.JOL_Employer
	  SET       
				JOL_Employer_Account = case @accountCount
										  when 0 then @JOL_Employer_Account
										  else JOL_Employer_Account
										end,
				JOL_Employer_Password = CASE @JOL_Employer_Password
										  WHEN '' THEN @Pwd
										  ELSE @JOL_Employer_Password
										END ,
				JOL_Employer_Name = case @nameCount
										when 0 then @JOL_Employer_Name
										else JOL_Employer_Name
									end,
				JOL_Employer_Industry = @JOL_Employer_Industry ,
				JOL_Employer_Type = @JOL_Employer_Type ,
				JOL_Employer_Province = @JOL_Employer_Province ,
				JOL_Employer_City = @JOL_Employer_City ,
				JOL_Employer_Address = @JOL_Employer_Address ,
				JOL_Employer_ZipCode = @JOL_Employer_ZipCode ,
				JOL_Employer_HomePage = @JOL_Employer_HomePage ,
				JOL_Employer_Introduction = @JOL_Employer_Introduction ,
				JOL_Employer_Linkman = @JOL_Employer_Linkman ,
				JOL_Employer_Mobile = @JOL_Employer_Mobile ,
				JOL_Employer_Email = @JOL_Employer_Email ,
				JOL_Employer_Phone = @JOL_Employer_Phone ,
				JOL_Employer_Fax = @JOL_Employer_Fax ,
				JOL_Employer_UpdateTime = GETDATE() ,
				JOL_Employer_VerifyStatus = @JOL_Employer_VerifyStatus ,
				JOL_Employer_VerifyUser = @JOL_Employer_VerifyUser ,
				JOL_Employer_VerifyTime = CASE @JOL_Employer_VerifyStatus
											WHEN '已审核' THEN GETDATE()
											ELSE NULL
										  END ,
				JOL_Employer_Credentials = @JOL_Employer_Credentials ,
				JOL_Employer_IsSchoolCorperate = @JOL_Employer_IsSchoolCorperate ,
				JOL_Employer_IsContactVisible = @JOL_Employer_IsContactVisible ,
				JOL_Employer_IsVIP = @JOL_Employer_IsVIP ,
				JOL_Employer_CredentialsFileLink = @JOL_Employer_CredentialsFileLink ,
				JOL_Employer_UpdateUser = @JOL_Employer_UpdateUser
	  WHERE     JOL_Employer_No = @JOL_Employer_No






GO
